import pymysql


db = None
def getConnection():
    # global db
    # if db is None:
    #     db = pymysql.connect(host="localhost",
    #                          user="root",
    #                          password="123456",
    #                          database="heima")
    # return db

    db = pymysql.connect(host="localhost",
                         user="root",
                         password="123456",
                         database="heima")
    return db


def close_database_connection():
    global db
    if db is not None:
        # 关闭数据库连接
        db.close()
        db = None


def get_prompt_byId(prompt_id):
    db = getConnection()
    cursor = db.cursor()
    sql = f'''select content from prompt where id = {prompt_id}'''
    print(sql)
    cursor.execute(sql)
    row = cursor.fetchone()
    if row:
        print(row[0])
        res = {"related_template": row[0]}
        print(res)
        return res
    cursor.close()
    db.close()


def get_prompt_url(project_id):
    db = getConnection()
    cursor = db.cursor()
    sql = f'''select url from project where id = "{project_id}"'''
    cursor.execute(sql)
    row = cursor.fetchone()
    if row:
        print(row[0])
        return row[0]
    cursor.close()
    db.close()


def set_prompt(project_id, prompt_id):
    db = getConnection()
    cursor = db.cursor()
    db.begin()
    sql = f'''update project set prompt_id = {prompt_id} where project_id = "{project_id}"'''
    cursor.execute(sql)
    db.commit()


# TODO 插入成功
def insert_user_prompt_relation(db, user_name, prompt_id):
    # db = getConnection()
    cursor = db.cursor()
    # print("===========")
    print(user_name, prompt_id)
    # sql = f'''insert into user_prompt_relation (user_id, prompt_id) values ({user_id}, {prompt_id})'''
    sql = f'''insert into user_prompt_relation (user_name, prompt_id) values ('{user_name}', {prompt_id})'''
    print("sql：" + sql)
    try:
        db.begin()
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(str(e))
        db.rollback()
    cursor.close()


# TODO ok
def insert_prompt(data):
    db = getConnection()
    user_name = data['user_name']
    title = data['title']
    # 1表示自定义
    type = 1
    content = data['content']
    # server_name = data['server_name']

    sql1 = f'''insert into prompt (title, type, content) values ('{title}', {type}, '{content}')'''
    print(sql1)
    cursor = db.cursor()
    response = {}
    try:
        db.begin()
        cursor.execute(sql1)
        # 获取新插入的prompt_id
        db.commit()
        cursor.execute("select last_insert_id()")
        last_id = cursor.fetchone()
        prompt_id = int(last_id[0])
        print(prompt_id)
        sql2 = f'''select user_id from user where user_name = "{user_name}"'''
        cursor.execute(sql2)
        res = cursor.fetchone()
        user_name = ""
        for row in res:
            user_name = row
        insert_user_prompt_relation(db, user_name, prompt_id)
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Creation failed: {str(e)}")
        response['message'] = "Creation failed"
    finally:
        cursor.close()
        db.close()
        return response


# 更新prompt
def update_prompt(data):
    db = getConnection()
    cursor = db.cursor()
    id = data['id']
    title = data['title']
    content = data['content']
    sql = f'''update prompt set title = '{title}', content = '{content}' where id = {id}'''
    print(sql)
    response = {}
    try:
        db.begin()
        cursor.execute(sql)
        db.commit()
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Transaction failed: {str(e)}")
        response['message'] = "Update failed"
        return "failed"
    finally:
        cursor.close()
        return response


# 根据promptId删除对应prompt表和user_prompt_relation中的记录
def delete_prompt(data):
    db = getConnection()
    prompt_id = data['prompt_id']
    sql1 = f'''DELETE FROM prompt where id = {prompt_id}'''
    print(sql1)
    sql2 = f'''delete from user_prompt_relation where prompt_id = {prompt_id}'''
    print(sql2)
    cursor = db.cursor()
    response = {}
    try:
        db.begin()
        cursor.execute(sql1)
        cursor.execute(sql2)
        db.commit()
        response['message'] = "ok"
    except Exception as e:
        db.rollback()
        print(f"Transaction failed: {str(e)}")
        response['message'] = "Deletion failed"
    finally:
        cursor.close()
        # db.close()
        return response


# 根据user_id返回这个用户拥有的prompt和全部预置的prompt
def query_all(uid):
    db = getConnection()
    sql = f'''SELECT * from prompt where id in 
    (select prompt_id from user_prompt_relation where user_id = {uid}) 
    UNION SELECT * from prompt where type = 0'''
    print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()

    cursor.close()
    return res


def query_all2(user_name):
    db = getConnection()
    sql = f'''SELECT * from prompt where id in 
    (select prompt_id from user_prompt_relation where user_name = '{user_name}') 
    UNION SELECT * from prompt where type = 0'''
    print(sql)
    cursor = db.cursor()
    cursor.execute(sql)
    res = cursor.fetchall()

    for row in res:
        print(row)

    cursor.close()
    db.close()
    return res


# def page_query(page_num, page_size=10):
#     db = getConnection()
#     offset = (page_num - 1) * page_size
#     cursor = db.cursor()
#     sql = f"""select * from prompt limit {page_size} offset {offset}"""
#     # print(sql)
#     cursor.execute(sql)
#     res = cursor.fetchall()
#     cursor.close()
#     # print(type(res))
#     return res


# 根据title模糊匹配
# def match_query(text):
#     db = getConnection()
#     text = "%" + text + "%"
#     sql = f'''select * from prompt where title like "{text}"'''
#     print(sql)
#     cursor = db.cursor()
#     cursor.execute(sql)
#     res = cursor.fetchall()
#     cursor.close()
#     return res


def get_user_prompt_relation():
    db = getConnection()
    cursor = db.cursor()
    sql = '''select * from user_prompt_relation'''
    cursor.execute(sql)
    res = cursor.fetchall()
    for row in res:
        print(row[0])
        print(row[1])
        print(row[2])
    cursor.close()


if __name__ == '__main__':

    # query_all2(1)
    #
    # data = {}
    # data['user_name'] = "admin"
    # data['title'] = "test8"
    # data['content'] = "test admin{}"
    # insert_prompt(data)
    # #

    db = getConnection()
    insert_user_prompt_relation(db, "admin", 4)

    # query_all2(1)


    # get_prompt_byId(11)
    # get_user_prompt_relation()

    # 删除prompt相关数据
    # data = {"prompt_id": 13}
    # delete_prompt(data)


    close_database_connection()